use std::collections::HashSet;
use chrono::{DateTime, Local};
use sqlite_wasm_rs::sqlite3_stmt;
use crate::db::dao::base_dao::Adder;
use crate::db::db::{Db, DbParamType};
use crate::db::pojo::pojo::{ImMessage, ImMessageActionData, ImMessageActionStatus, ImMessageStatus, MessageType};
use crate::db::pojo::pojo_other::ImHotMessageTotal;
use crate::db::util::{db_get_enum, db_get_int, db_get_int64, db_get_option_date_m, db_get_option_enum, db_get_string, db_get_time};

const SELECT_COLUMNS: &str = "id, clientId, currentUserId, serverId, fromUserId, toUserId, messageType, content, groupCode, sessionId, ext, ext1, ext2, version,
    refType, refMessageIds, status, fileStatus, actionStatus, serverReceiveTime, createTime, updateTime";


pub fn message_inserts(db: Db, messages: &mut Vec<&mut ImMessage>) {
    if messages.is_empty() { return; }
    let sql = "insert into im_message(clientId, currentUserId, fromUserId, toUserId, serverId, messageType, content, groupCode, sessionId, ext, ext1, ext2, status,
        fileStatus, actionStatus, serverReceiveTime, createTime, updateTime) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    let mut params_list = vec![];
    for message in messages.iter() {
        let mut params = vec![];
        params.push(DbParamType::Str(&*message.client_id));
        params.push(DbParamType::Str(&*message.current_user_id));
        params.push(DbParamType::Str(&*message.from_user_id));
        params.push(DbParamType::OptionStr(message.to_user_id.as_ref()));
        params.push(DbParamType::OptionStr(message.server_id.as_ref()));
        params.push(DbParamType::String(format!("{:?}", message.message_type.clone())));
        params.push(DbParamType::Str(&*message.content));
        params.push(DbParamType::OptionStr(message.group_code.as_ref()));
        params.push(DbParamType::Uint(message.session_id));
        params.push(DbParamType::OptionStr(message.ext.as_ref()));
        params.push(DbParamType::OptionStr(message.ext1.as_ref()));
        params.push(DbParamType::OptionStr(message.ext2.as_ref()));
        params.push(DbParamType::Byte(message.status.clone() as i8));
        params.push(DbParamType::Byte(message.file_status.clone() as i8));
        params.push(DbParamType::Byte(message.action_status.clone() as i8));
        params.push(DbParamType::DateTimeM3(&message.server_receive_time));
        params.push(DbParamType::DateTime2(&message.create_date_time));
        params.push(DbParamType::DateTime2(&message.update_date_time));

        params_list.push(params);
    }

    log::info!("save messages, params list: {:?}", params_list);
    let ids = db.inserts2("message insert", sql, params_list);
    assert_eq!(ids.len(), messages.len(), "批量插入数据没有都生成主键(message)");
    for (idx, value) in ids.iter().enumerate() {
        let message = messages.get_mut(idx).unwrap();
        message.id = Some(value.clone());
    }
}

pub fn message_updates(db: Db, messages: &mut Vec<&mut ImMessage>) {
    if messages.is_empty() { return; }
    let sql = "update im_message set fromUserId = ?, toUserId = ?, messageType = ?, content = ?, groupCode = ?,
        sessionId =?, ext = ?,  status = ?, fileStatus = ?, actionStatus = ?, serverReceiveTime = ?,  updateTime=? where currentUserId = ? and id = ?";

    let mut params_list = vec![];
    for message in messages.iter() {
        let mut params = vec![];
        params.push(DbParamType::Str(&*message.from_user_id));
        params.push(DbParamType::OptionStr(message.to_user_id.as_ref()));
        params.push(DbParamType::String(format!("{:?}", message.message_type.clone())));
        params.push(DbParamType::Str(&*message.content));
        params.push(DbParamType::OptionStr(message.group_code.as_ref()));
        params.push(DbParamType::Uint(message.session_id.clone()));
        params.push(DbParamType::OptionStr(message.ext.as_ref()));
        params.push(DbParamType::Byte(message.status.clone() as i8));
        params.push(DbParamType::Byte(message.file_status.clone() as i8));
        params.push(DbParamType::Byte(message.action_status.clone() as i8));
        params.push(DbParamType::DateTimeM3(&message.server_receive_time));
        params.push(DbParamType::DateTime2(&message.update_date_time));

        params.push(DbParamType::Str(&*message.current_user_id));
        params.push(DbParamType::Uint(message.id.clone().unwrap()));
        params_list.push(params);
    }

    log::info!("message update params list: {:?}", params_list);
    db.batch_exec("message updates", sql.to_string(), params_list);
}

pub fn message_deletes(db: Db, user_id: String, messages: Vec<&mut ImMessage>) {
    if messages.is_empty() { return; }

    let sql = "update im_message set status = 9 where currentUserId = ? and serverId = ?";

    let mut params_list = vec![];
    for message in messages.iter() {
        let mut params = vec![];
        params.push(DbParamType::Str(&user_id));
        params.push(DbParamType::OptionStr(message.server_id.as_ref()));

        params_list.push(params);
    }
    
    db.batch_exec("message delete", sql.to_string(), params_list);
}

pub fn update_message_status(db: Db, message_id: u64, message_status: ImMessageStatus, action_status: ImMessageActionStatus ) {
    let sql ="update im_message set status = ?, actionStatus = ?,  updateTime=? where id = ?";

    let mut params = vec![];
    params.push(DbParamType::Byte(message_status as i8));
    params.push(DbParamType::Byte(action_status as i8));
    params.push(DbParamType::DateTime(Local::now()));
    params.push(DbParamType::Uint(message_id));

    db.exec2("message update", sql.to_string(), params);
}

pub fn change_message_action_status(db: Db, message_action_data_list: Vec<ImMessageActionData>) {
    if message_action_data_list.is_empty() { return; }

    let sql ="update im_message set actionStatus = ?, updateTime=? where id = ?";
    let mut params_list = vec![];
    for message_action_data in message_action_data_list.iter() {
        let mut params = vec![];
        params.push(DbParamType::Byte(message_action_data.action_status.clone() as i8));
        params.push(DbParamType::DateTime(Local::now()));
        params.push(DbParamType::Uint(message_action_data.message_id));
        params_list.push(params);
    }
    db.batch_exec("message status", sql.to_string(), params_list);
}

pub fn change_message_action_status2(db: Db, ids: HashSet<u64>, action_status: ImMessageActionStatus) {
    if ids.is_empty() { return; }
    
    let sql_where = ids.iter().map(|_id| "?").collect::<Vec<&str>>().join(",");
    let sql = format!("update im_message set actionStatus = ?, updateTime=? where id in ({})", &sql_where);
    let mut params = vec![];
    params.push(DbParamType::Byte(action_status as i8));
    params.push(DbParamType::DateTime(Local::now()));
    for id in ids.iter() {
        params.push(DbParamType::Uint(*id));
    }
    db.exec2("message change", sql, params);
}

pub fn change_message_action_status3(db: Db, user_id: &str, ids: HashSet<String>, action_status: ImMessageActionStatus) {
    if ids.is_empty() { return; }

    let sql_where = ids.iter().map(|_id| "?").collect::<Vec<&str>>().join(",");
    let sql = format!("update im_message set actionStatus = ?, updateTime=? where currentUserId = ? and serverId in ({})", &sql_where);
    let mut params = vec![];
    params.push(DbParamType::Byte(action_status as i8));
    params.push(DbParamType::DateTime(Local::now()));
    params.push(DbParamType::Str(user_id));
    for id in ids.into_iter() {
        params.push(DbParamType::String(id));
    }
    db.exec2("message change", sql, params);
}


pub fn message_total(db: Db)-> usize {
    let size = db.count("message total", c"select count(*) from im_message", Vec::new());
    log::info!("db message count: {}", size);
    size
}

pub fn message_send_total(db: Db)-> usize {
    let size = db.count("message total", c"select count(*) from im_message_send", Vec::new());
    log::info!("db message send count: {}", size);
    size
}

pub fn query_need_send_messages_by_user_id(db: Db, user_id: &str, size: usize)-> Vec<ImMessage> {
    let sql = format!("select {} from im_message where currentUserId = ? and actionStatus in (0, 1, 6) and status not in (3,9,12,30) limit {}",
                      SELECT_COLUMNS, size);
    let mut params = vec![];
    params.push(DbParamType::Str(user_id));
    log::info!("query syn messages params: {:?}, {}", params, sql);
    let ms = db.query2("message query", &sql, params, Box::new(message_wrap));
    log::info!("query syn messages result: {:?}", ms);
    ms
}

pub fn query_need_syn_action_status_messages_by_user_id(db: Db, user_id: &str, size: usize)-> Vec<ImMessage> {
    let sql = format!("select {} from im_message where currentUserId = ? and fromUserId = ? and status = 1 order by id asc limit 0, {}", &SELECT_COLUMNS, size);
    let mut params = vec![];
    params.push(DbParamType::Str(user_id));
    params.push(DbParamType::Str(user_id));
    db.query2("message query", &sql, params, Box::new(message_wrap))
}

pub fn query_message_by_server_id(db: Db, current_user_id: &str, server_id: &str) -> Option<ImMessage> {
    let sql = format!("select {} from im_message where currentUserId = ? and (clientId = ? or serverId = ?) and status not in (9,11,12)", &SELECT_COLUMNS);
    let mut params = vec![];
    params.push(DbParamType::Str(current_user_id));
    params.push(DbParamType::Str(server_id.clone()));
    params.push(DbParamType::Str(server_id));
    db.query_one("message query2", sql.to_string(), params, Box::new(message_wrap))
}

pub fn query_message_by_server_ids(db: Db, current_user_id: &str, server_ids: Vec<&str>) -> Vec<ImMessage> {
    let sql_where = server_ids.iter().map(|_message_id| "?").collect::<Vec<&str>>().join(",");
    let sql = format!("select {} from im_message where currentUserId = ? and serverId in ({})", &SELECT_COLUMNS, sql_where);

    let mut params = vec![];
    params.push(DbParamType::Str(current_user_id));
    for message_id in server_ids.iter() {
        params.push(DbParamType::Str(message_id));
    }

    db.query2("message query3", sql.as_str(), params, Box::new(message_wrap))
}

pub fn query_messages_by_session_id(db: Db, session_id: u64, server_date_time: Option<DateTime<Local>>, size: usize) -> Vec<ImMessage> {
    let sql_where = match server_date_time {
        None => {" and 1=1"}
        Some(_server_date_time) => {
            " and serverReceiveTime>= ?"
        }
    };
    let sql = format!("select {} from im_message where sessionId = ? {} and status in (1, 3, 5, 11) order by serverReceiveTime desc limit 0, {}",
                      &SELECT_COLUMNS, sql_where, size);

    let mut params = vec![];
    params.push(DbParamType::Uint(session_id));
    if let Some(date_time) = server_date_time {
        params.push(DbParamType::DateTimeM(date_time));
    }

    let mut result = db.query2("message query4", sql.as_str(), params, Box::new(message_wrap));
    result.reverse();
    result
}

pub fn query_messages(db: Db, size: usize) -> Vec<ImMessage> {
    let sql = format!("select {} from im_message order by id desc limit 0, {}", &SELECT_COLUMNS, size);

    let params = vec![];

    db.query2("message query5", sql.as_str(), params, Box::new(message_wrap))
}

pub fn query_hot_message_total(db: Db, user_id: &str, start_date: DateTime<Local>) ->Vec<ImHotMessageTotal> {
    log::info!("query hot message total, {:?}", start_date);
    let sql = "select sessionId, count(*) total from im_message where currentUserId = ? and status in (1, 3) and actionStatus in(0)
        and ((toUserId = ? and fromUserId != ?) or (groupCode is not null and fromUserId != ?)) and serverReceiveTime >= '2025-07-30 10:11:52.045' group by sessionId";

    let mut params = vec![];
    params.push(DbParamType::Str(&user_id));
    params.push(DbParamType::Str(&user_id));
    params.push(DbParamType::Str(&user_id));
    params.push(DbParamType::Str(&user_id));
    // params.push(DbParamType::DateTime(start_date));
    log::info!("query hot message total params, {:?}, {}", params, sql);
    db.query2("message query6", sql, params, Box::new(hot_message_wrap))
}


fn message_wrap(stmt: *mut sqlite3_stmt) -> ImMessage {
    unsafe {
        let mut adder = Adder::new();

        let id = db_get_int64(stmt, adder.add());
        let client_id = db_get_string(stmt, adder.add()).unwrap();
        let current_user_id = db_get_string(stmt, adder.add()).unwrap();
        let server_id = db_get_string(stmt, adder.add());
        let from_user_id = db_get_string(stmt, adder.add()).unwrap();
        let to_user_id = db_get_string(stmt, adder.add());
        let message_type = db_get_string(stmt, adder.add()).unwrap();
        let content = db_get_string(stmt, adder.add()).unwrap();
        let group_code = db_get_string(stmt, adder.add());
        let session_id = db_get_int64(stmt, adder.add());
        let ext = db_get_string(stmt, adder.add());
        let ext1 = db_get_string(stmt, adder.add());
        let ext2 = db_get_string(stmt, adder.add());
        let version = db_get_int64(stmt, adder.add());
        let ref_type = db_get_option_enum(stmt, adder.add());
        let ref_message_ids = db_get_string(stmt, adder.add());
        let status = db_get_enum(stmt, adder.add());
        let file_status = db_get_enum(stmt, adder.add());
        let action_status = db_get_enum(stmt, adder.add());

        let server_receive_time = db_get_option_date_m(stmt, adder.add());
        let create_date_time = db_get_time(stmt, adder.add());
        let update_date_time = db_get_time(stmt, adder.add());

        log::info!("message type value, {}", message_type);
        let message_type = MessageType::from(message_type.as_str());

        ImMessage {
            id: Some(id),
            client_id,
            current_user_id,
            from_user_id,
            to_user_id,
            server_id,
            message_type,
            group_code,
            content,
            session_id,
            ref_type,
            ref_message_ids,
            ext,
            ext1,
            ext2,
            version,
            status,
            file_status,
            action_status,
            server_receive_time,
            create_date_time,
            update_date_time,
            
            message_send_set: None,
            session: None,
        }
    }
}

fn hot_message_wrap(stmt: *mut sqlite3_stmt) -> ImHotMessageTotal {
    unsafe {
        let mut adder = Adder::new();

        let session_id = db_get_int64(stmt, adder.add());
        let size = db_get_int(stmt, adder.add()) as usize;

        ImHotMessageTotal {
            session_id,
            size,
        }
    }
}